package com.tuanzi.loan.business.repository.statistics.impl;


import com.tuanzi.loan.business.repository.statistics.CheckUserStatRepository;
import com.tuanzi.loan.core.repository.JOOQBaseRepositoryPlus;
import com.tuanzi.loan.vo.request.check.CheckUserStatRequest;
import com.tuanzi.loan.vo.response.check.CheckUserStatResponse;
import org.jooq.*;
import org.jooq.impl.DSL;
import org.jooq.impl.TableImpl;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.domain.Sort;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.*;

import static com.tuanzi.loan.generated.jooq.stat.tables.StatCheckUserCollection.STAT_CHECK_USER_COLLECTION;
import static com.tuanzi.loan.generated.jooq.stat.tables.StatHitSuccess.STAT_HIT_SUCCESS;

/**
 * 撞库统计
 *
 * created by chuIllusions_tan 20180727
 */
@Repository
public class CheckUserStatRepositoryImpl extends JOOQBaseRepositoryPlus implements CheckUserStatRepository {

    private static final String ALL_FIELD = "'全部'";
    private static final String ALL_FILED_VALUE = "全部";

    private static final int CHECK_SUCCESS = 1;
    private static final int CHECK_FAILURE = 0;

    private static final String TIME = "time";
    private static final String PRODUCT_ID = "product_id";
    private static final String CHANNEL_ID = "channel_id";
    private static final String POSITION_ID = "position_id";
    private static final String FUNCTION_ID = "function_id";
    private static final String FILTER_TYPE = "type";
    private static final String PLATFORM = "platform";
    private static final String SUCCESS_FILED = "check_uv_success";
    private static final String FAILURE_FILED = "check_uv_failure";

    @Autowired
    private ActivityParticipateStatRepositoryImpl activityParticipateStatRepository;

    private static Map<String,String> FILTER_TYPES_MAP = new HashMap<>();
    static {
        FILTER_TYPES_MAP.put("list_type","列表过滤");
        FILTER_TYPES_MAP.put("detail_type","详情过滤");
    }

    @Override
    protected TableImpl getTable() {
        return STAT_HIT_SUCCESS;
    }

    @Override
    public Page<CheckUserStatResponse> list(CheckUserStatRequest searchRequest, Pageable pageable) {

        fields(STAT_HIT_SUCCESS.CDATE);
        groupBy(STAT_HIT_SUCCESS.CDATE);
        orderBy(STAT_HIT_SUCCESS.CDATE, Sort.Direction.DESC);
        if (Objects.nonNull(searchRequest.getStartDate())) {
            ge(STAT_HIT_SUCCESS.CDATE, (new java.sql.Date(searchRequest.getStartDate().getTime())));
        }
        if (Objects.nonNull(searchRequest.getEndDate())) {
            le(STAT_HIT_SUCCESS.CDATE, (new java.sql.Date(searchRequest.getEndDate().getTime())));
        }

        // 渠道
        if (Objects.isNull(searchRequest.getChannels()) || searchRequest.getChannels().isEmpty()) {
            // 全部展开
            fields(STAT_HIT_SUCCESS.CHANNEL);
            groupBy(STAT_HIT_SUCCESS.CHANNEL);
        } else if (isSelectQueryAll(searchRequest.getChannels())) {
            // 查询全部

        } else {
            fields(STAT_HIT_SUCCESS.CHANNEL);
            groupBy(STAT_HIT_SUCCESS.CHANNEL);
            in(STAT_HIT_SUCCESS.CHANNEL, (searchRequest.getChannels()));
        }

        // 产品
        if (Objects.isNull(searchRequest.getProducts()) || searchRequest.getProducts().isEmpty()) {
            // 全部展开
            fields(STAT_HIT_SUCCESS.PRD_ID);
            groupBy(STAT_HIT_SUCCESS.PRD_ID);
        } else if (isSelectQueryAll(searchRequest.getProducts())) {
            // 全部展开后汇总

        } else {
            fields(STAT_HIT_SUCCESS.PRD_ID);
            groupBy(STAT_HIT_SUCCESS.PRD_ID);
            in(STAT_HIT_SUCCESS.PRD_ID, (searchRequest.getProducts()));
        }

        // 平台
        if (Objects.isNull(searchRequest.getPlatforms()) || searchRequest.getPlatforms().isEmpty()) {
            // 全部展开
            fields(STAT_HIT_SUCCESS.PLATFORM);
            groupBy(STAT_HIT_SUCCESS.PLATFORM);
        } else if (isSelectQueryAll(searchRequest.getPlatforms())) {
            // 查询全部

        } else {
            fields(STAT_HIT_SUCCESS.PLATFORM);
            groupBy(STAT_HIT_SUCCESS.PLATFORM);
            in(STAT_HIT_SUCCESS.PLATFORM, (searchRequest.getPlatforms()));
        }

        // 用户类型
        if (Objects.isNull(searchRequest.getTypes()) || searchRequest.getTypes().isEmpty()) {
            // 全部展开
            fields(STAT_HIT_SUCCESS.NEW_USER);
            groupBy(STAT_HIT_SUCCESS.NEW_USER);
        } else if (isSelectQueryAll(searchRequest.getTypes())) {
            // 查询全部

        } else {
            fields(STAT_HIT_SUCCESS.NEW_USER);
            groupBy(STAT_HIT_SUCCESS.NEW_USER);
            in(STAT_HIT_SUCCESS.NEW_USER, (searchRequest.getTypes()));
        }

        // 甲方产品
        if (Objects.isNull(searchRequest.getHitType()) || searchRequest.getHitType().isEmpty()) {
            fields(STAT_HIT_SUCCESS.HIT_LIB_TYPE);
            groupBy(STAT_HIT_SUCCESS.HIT_LIB_TYPE);
            // 查询全部
        } else if (isSelectQueryAll(searchRequest.getHitType())){

        }else {
            fields(STAT_HIT_SUCCESS.HIT_LIB_TYPE);
            groupBy(STAT_HIT_SUCCESS.HIT_LIB_TYPE);
            in(STAT_HIT_SUCCESS.HIT_LIB_TYPE,searchRequest.getHitType());
        }

        fields(DSL.sum(STAT_HIT_SUCCESS.HIT_SUCCESS).filterWhere(STAT_HIT_SUCCESS.NEW_USER.eq((byte)1)).as("hit_check_new"),
                DSL.sum(STAT_HIT_SUCCESS.HIT_SUCCESS).as("hit_sucess_count"));


        return search(pageable, record -> {
            CheckUserStatResponse response = new CheckUserStatResponse();
            response.setTime(record.get(STAT_HIT_SUCCESS.CDATE));
            // 产品
            if (isSelectQueryAll(searchRequest.getProducts())) {
                // 全部展开后汇总
                response.setProduct("全部");
            } else {
                Integer integer = record.get(STAT_HIT_SUCCESS.PRD_ID, Integer.class);
                String product = activityParticipateStatRepository.getProdMap().getOrDefault(integer.toString(), integer.toString());
                response.setProduct(product);
            }

            // 渠道
            if (isSelectQueryAll(searchRequest.getChannels())) {
                // 查询全部
                response.setChannel("全部");
            } else {
                String channel = activityParticipateStatRepository.getChannelMap().getOrDefault(record.get(STAT_HIT_SUCCESS.CHANNEL, Integer.class).toString(), record.get(STAT_HIT_SUCCESS.CHANNEL, String.class));
                response.setChannel(channel);
            }

            if (isSelectQueryAll(searchRequest.getPlatforms())) {
                // 查询全部
                response.setPlatform("全部");
            } else {
                response.setPlatform(record.get(STAT_HIT_SUCCESS.PLATFORM, String.class) == null ? "null" : record.get(STAT_HIT_SUCCESS.PLATFORM, String.class));
            }

            // 用户类型
            if (isSelectQueryAll(searchRequest.getTypes())) {
                // 查询全部
                response.setType("全部");
            } else {
                response.setType(record.get(STAT_HIT_SUCCESS.NEW_USER, Integer.class) == null ? "null" : record.get(STAT_HIT_SUCCESS.NEW_USER, Integer.class) == 1 ? "新用户" : "老用户");
            }

            // 甲方产品
            if (isSelectQueryAll(searchRequest.getHitType())) {
                response.setActivity("全部");
            }else {
                response.setActivity(record.get(STAT_HIT_SUCCESS.HIT_LIB_TYPE,String.class));
            }
            if (searchRequest.getHitType()==null){
                response.setIds("全部");
            }else{
                response.setIds(record.get(STAT_HIT_SUCCESS.HIT_LIB_TYPE,String.class));

            }

            response.setCheck_success(record.get("hit_sucess_count", Integer.class));
            response.setCheck_new((record.get("hit_check_new",Long.class)==null?0L:record.get("hit_check_new",Long.class)));
            return response;
        });
    }

    @Override
    public List<CheckUserStatResponse> getDataList(CheckUserStatRequest searchRequest) {
        return null;
    }

    /**
     * 根据请求条件，构造查询
     */
    private SelectQuery createQuery(CheckUserStatRequest searchRequest){

        Table successTable = generateSelect(searchRequest, CHECK_SUCCESS).asTable("success");
        Table failureTable = generateSelect(searchRequest, CHECK_FAILURE).asTable("failure");

        return dslContext.select(
                successTable.field(TIME),
                successTable.field(PRODUCT_ID),
                successTable.field(CHANNEL_ID),
                successTable.field(POSITION_ID),
                successTable.field(FUNCTION_ID),
                successTable.field(PLATFORM),
                successTable.field(FILTER_TYPE),
                successTable.field(SUCCESS_FILED),
                failureTable.field(FAILURE_FILED)
        ).from(successTable)
                .leftJoin(failureTable).on(successTable.field(TIME).eq(failureTable.field(TIME)))
                .and(successTable.field(PRODUCT_ID).eq(failureTable.field(PRODUCT_ID)))
                .and(successTable.field(CHANNEL_ID).eq(failureTable.field(CHANNEL_ID)))
                .and(successTable.field(POSITION_ID).eq(failureTable.field(POSITION_ID)))
                .and(successTable.field(FUNCTION_ID).eq(failureTable.field(FUNCTION_ID)))
                .and(successTable.field(PLATFORM).eq(failureTable.field(PLATFORM)))
                .and(successTable.field(FILTER_TYPE).eq(failureTable.field(FILTER_TYPE)))
                .getQuery();
    }


    /**
     * double类型保留两位小数
     */
    private static double doubleScale(double param,int scale){
        BigDecimal bigDecimal = new BigDecimal(param);
        return bigDecimal.setScale(scale, BigDecimal.ROUND_HALF_UP).doubleValue();
    }

    private SelectQuery<Record> generateSelect(CheckUserStatRequest searchRequest, int check) {

        Condition condition = DSL.trueCondition();

        List<SelectField<?>> selectFields = new ArrayList<>();
        selectFields.add(DSL.field(STAT_CHECK_USER_COLLECTION.TIME));

        String name = check == 0 ? FAILURE_FILED : SUCCESS_FILED;
        selectFields.add(DSL.sum(STAT_CHECK_USER_COLLECTION.CHECK_UV).as(name));

        List<GroupField> groupFields = new ArrayList<>();
        groupFields.add(DSL.field(STAT_CHECK_USER_COLLECTION.TIME));

        //处理其他参数
        condition = checkConditions(STAT_CHECK_USER_COLLECTION.APP_PRD_ID,PRODUCT_ID,searchRequest.getProducts(),selectFields,groupFields,condition);
        //channelid
        condition = checkConditions(STAT_CHECK_USER_COLLECTION.CHANNEL,CHANNEL_ID,searchRequest.getChannels(),selectFields,groupFields,condition);
        //platform
        condition = checkConditions(STAT_CHECK_USER_COLLECTION.PLATFORM,PLATFORM,searchRequest.getPlatforms(),selectFields,groupFields,condition);
        //filterType
        condition = checkConditions(STAT_CHECK_USER_COLLECTION.TYPE,FILTER_TYPE,searchRequest.getTypes(),selectFields,groupFields,condition);
        //ads
        condition = checkConditions(STAT_CHECK_USER_COLLECTION.POSITION,POSITION_ID,searchRequest.getHitType(),selectFields,groupFields,condition);
        //ACTIVITIES
        condition = checkConditions(STAT_CHECK_USER_COLLECTION.FUNCTIONID,FUNCTION_ID,searchRequest.getActivities(),selectFields,groupFields,condition);
        //主条件
        condition = condition.and(STAT_CHECK_USER_COLLECTION.STATUS.eq(check));
        //时间
        SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
        if (Objects.nonNull(searchRequest.getStartDate())){
            condition = condition.and(DSL.field(STAT_CHECK_USER_COLLECTION.TIME).between(
                    sdf.format(searchRequest.getStartDate()),sdf.format(searchRequest.getEndDate())));
        }

        return dslContext.select(selectFields)
                .from(STAT_CHECK_USER_COLLECTION)
                .where(condition)
                .groupBy(groupFields)
                .orderBy(STAT_CHECK_USER_COLLECTION.TIME.desc())
                .getQuery();
    }

    /**
     * 创建检查条件
     */
    private Condition checkConditions(TableField tableField, String as, List checkList, List<SelectField<?>> selectFields, List<GroupField> groupFields, Condition condition){
        if (Objects.nonNull(checkList) && checkList.isEmpty()){
            selectFields.add(tableField.as(as));
            groupFields.add(tableField);
        } else if(activityParticipateStatRepository.isSelectQueryAll(checkList)){
            selectFields.add(DSL.field(ALL_FIELD).as(as));
        } else {
            selectFields.add(tableField.as(as));
            groupFields.add(tableField);
            condition = condition.and(tableField.in(checkList));
        }
        return condition;
    }

    private boolean isSelectQueryAll(List selectList) {
        if (selectList == null || selectList.size() == 0) {
            return false;
        }
        for (int i = 0; i < selectList.size(); i++) {
            String s = String.valueOf(selectList.get(i));
            if ("-1000".equals(s)) {
                return true;
            }
        }
        return false;
    }

}
